package com.hfstu.framework.db;




import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.StringTokenizer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
import com.hfstu.framework.util.FileInputStreamUtil;

/**
 * 
 * @author :dejunx
 * @datetime:2011-8-31下午2:55:43
 * @功能:数据库连接池（封装bonecp）
 */
public class DBConnectionManager {

	private static DBConnectionManager instance = null; // 唯一实例
	private static Map<String, BoneCP> pools = new HashMap<String, BoneCP>();
	private static List<Driver> drivers = new ArrayList<Driver>(); // 驱动
	private final static String FILE_NAME = "db.properties";

	private final static Integer MIN_CONNECTION_NUM = 5;// 默认最小连接数
	private final static Integer MAX_CONNECTION_NUM = 10;// 默认最大连接数
	private static Log log = LogFactory.getLog(DBConnectionManager.class
			.getName());

	public static synchronized DBConnectionManager getInstance(String fileName) {
		if (instance == null) {
			instance = new DBConnectionManager(fileName);
		}

		return instance;
	}

	public static synchronized DBConnectionManager getInstance() {
		return getInstance(null);
	}

	private DBConnectionManager(String fileName) {
		if (!(fileName == null || "".equals(fileName.trim()))) {
			init(fileName);
		} else {
			init(FILE_NAME);
		}
	}

	/**
	 * 初始化配置文件
	 * 
	 * @param fileName
	 */
	private void init(String fileName) {
		try {
			InputStream is = FileInputStreamUtil.getFileInputStream(
					DBConnectionManager.class, fileName);
			Properties dbProps = new Properties();
			dbProps.load(is);

			loadDrivers(dbProps);
			createPools(dbProps);
		} catch (Exception e) {
			log.error("不能读取属性文件 " + fileName + "请确保在CLASSPATH指定的路径中 "
					+ ", 错误: " + e);
		}

	}

	/**
	 * 装载和注册驱动
	 * 
	 * @param props
	 */
	private void loadDrivers(Properties props) {
		String driverClasses = props.getProperty("drivers");
		StringTokenizer st = new StringTokenizer(driverClasses);
		while (st.hasMoreElements()) {
			String driverClassName = st.nextToken().trim();
			try {
				Driver driver = (Driver) Class.forName(driverClassName)
						.newInstance();
				DriverManager.registerDriver(driver);
				drivers.add(driver);
				if (log.isDebugEnabled()) {
					log.debug("成功注册JDBC驱动程序" + driverClassName);
				}
			} catch (Exception e) {
				log.error("无法注册JDBC驱动程序: " + driverClassName + ", 错误: " + e);
			}
		}
	}

	/**
	 * 创建连接池
	 * 
	 * @param props
	 */
	private void createPools(Properties props) {
		Enumeration<?> propNames = props.propertyNames();
		while (propNames.hasMoreElements()) {
			String name = (String) propNames.nextElement();
			if (name.endsWith(".poolType")) {

				String poolName = name.substring(0, name.lastIndexOf("."));
				String poolType = props.getProperty(poolName + ".poolType");
				if ("BoneCP".equalsIgnoreCase(poolType)) {
					String jdbcUrl = props.getProperty(poolName + ".jdbcUrl");
					if (jdbcUrl == null) {
						log.debug("没有为连接池" + poolName + "指定URL");
						continue;
					}
					String minConnectionsPerPartition = props.getProperty(
							poolName + ".minConnectionsPerPartition",
							String.valueOf(MIN_CONNECTION_NUM));
					String maxConnectionsPerPartition = props.getProperty(
							poolName + ".maxConnectionsPerPartition",
							String.valueOf(MAX_CONNECTION_NUM));
					String partitionCount = props.getProperty(poolName
							+ ".partitionCount", "1");
					String username = props.getProperty(poolName + ".username");
					String password = props.getProperty(poolName + ".password");
					BoneCP connectionPool = createBoneCp(jdbcUrl, username,
							password, minConnectionsPerPartition,
							maxConnectionsPerPartition, partitionCount);
					if (connectionPool == null) {
						log.error(poolName + " connection pools create false !");
						continue;
					}
					pools.put(poolName, connectionPool);
				} else if ("proxool".equals(poolType)) {

				}

			}
		}
	}

	/**
	 * 创建bonecp数据库连接池
	 * 
	 * @param jdbcUrl
	 * @param username
	 * @param password
	 * @param minConnectionsPerPartition
	 * @param maxConnectionsPerPartition
	 * @param partitionCount
	 * @return
	 */
	private BoneCP createBoneCp(String jdbcUrl, String username,
			String password, String minConnectionsPerPartition,
			String maxConnectionsPerPartition, String partitionCount) {
		BoneCP connectionPool = null;
		try {
			BoneCPConfig config = new BoneCPConfig();
			config.setJdbcUrl(jdbcUrl);
			config.setUsername(username);
			config.setPassword(password);
			config.setMinConnectionsPerPartition(Integer
					.valueOf(maxConnectionsPerPartition));
			config.setMaxConnectionsPerPartition(Integer
					.valueOf(maxConnectionsPerPartition));
			config.setPartitionCount(Integer.valueOf(partitionCount));
			connectionPool = new BoneCP(config);
		} catch (Exception e) {
			System.out.println("连接获取失败");
		}
		return connectionPool;
	}

	/**
	 * 得到连接
	 * 
	 * @param name
	 * @return
	 */
	public synchronized Connection getConnection(String name) {

		BoneCP connectionPool = (BoneCP) pools.get(name);
		if (connectionPool != null) {

			
			try {
				Connection conn = connectionPool.getConnection();
				if (log.isInfoEnabled()) {
					log.info("===>>>>>>=== poolNames = " + name + ","
							+ " TotalCreatedConnections = "
							+ connectionPool.getTotalCreatedConnections() + ","
							+ " TotalFree = " + connectionPool.getTotalFree()
							+ "," + " TotalLeased = "
							+ connectionPool.getTotalLeased() + "===>>>>>>===");
				}
				return conn;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 关闭连接
	 * 
	 * @param poolName
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public void close(String poolName, Connection conn, Statement stmt,
			ResultSet rs) {
		close(stmt, rs);
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			log.error("Close conn Error : " + se);
		}
	}

	public static void close(Statement stmt, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException se) {
			log.error("Close ResultSet Error : " + se);
		}

		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException se) {
			log.error("Close Statement Error : " + se);
		}

	}

	/**
	 * 关闭所有连接,撤销驱动程序的注册
	 */
	public synchronized void release() {
		Set<String> keySet = pools.keySet();
		Iterator<String> keys = keySet.iterator();
		while (keys.hasNext()) {
			BoneCP pool = (BoneCP) pools.get(keys.next());
			pool.shutdown();
		}
		Iterator<Driver> allDrivers = drivers.iterator();
		while (allDrivers.hasNext()) {
			Driver driver = (Driver) allDrivers.next();
			try {
				DriverManager.deregisterDriver(driver);
				if (log.isDebugEnabled()) {
					log.debug("撤销JDBC驱动程序 " + driver.getClass().getName()
							+ "的注册");
				}
			} catch (SQLException e) {
				log.error("无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName()
						+ e);
			}
		}
	}

	public static void main(String[] args) {
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		Connection con = connMgr.getConnection("ifs");
		if (con == null) {
			System.out.println("不能获取数据库连接.");
			return;
		}
		ResultSet rs = null;
		ResultSetMetaData md = null;
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			rs = stmt
					.executeQuery("select count(*) from sword_zjhw_staff");
			int count = 0;
			while (rs.next()) {
				count = rs.getInt(1);
				break;
			}
			int totalPages = (count + 15 - 1) / 15;
			for (int i = 1; i <= totalPages; i++) {
				StringBuffer sql = new StringBuffer();
				sql.append("select * from sword_zjhw_weighbridge")
						.append(" limit ").append((i - 1) * 15).append(",")
						.append(15);
				System.out.println(sql.toString());
				rs = stmt.executeQuery(sql.toString());
				md = rs.getMetaData();
				int flag = 0;
				while (rs.next()) {
					flag++;
					System.out.print(flag + "	");
					for (int j = 1; j <= md.getColumnCount(); j++) {

						System.out.print(md.getColumnTypeName(j) + ":"
								+ md.getColumnName(j) + ":" + rs.getString(j)
								+ "	");
					}
					System.out.println("\n");
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connMgr.close("ifs", con, stmt, rs);
			//connMgr.release();
		}
	}
}
